package com.basis.message;

import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class SqlParserUtil {
	/**
	 * 方法的主要入口
	 * 
	 * @param sql:要解析的sql语句
	 * @return 返回解析结果
	 */
	public String getParsedSql(String sql) {
		sql = sql.trim();
		sql = sql.toLowerCase();
		sql = sql.replaceAll("\\s{1,}", " ");
		sql = "" + sql + " ENDOFSQL";
		return SingleSqlParserFactory.generateParser(sql).getParsedSql();
	}
	/** */
	/**
	 * SQL语句解析的接口
	 * 
	 * @param sql:要解析的sql语句
	 * @return 返回解析结果
	 */
	public List<SqlSegment> getParsedSqlList(String sql) {
		sql = sql.trim();
		sql = sql.toLowerCase();
		sql = sql.replaceAll("\\s{1,}", " ");
		sql = "" + sql + " ENDOFSQL";
		return SingleSqlParserFactory.generateParser(sql).RetrunSqlSegments();
	}
	public static void main(String[] args) {
		// String test="select a from b " +
		// "\n"+"where a=b";
		// test=test.replaceAll("\\s{1,}", " ");
		// 程序的入口
		String testSql = "select case when abc.bdd.e > 0 and abc.eee.a = 1 then 1 else 0 end from dual";
		SqlParserUtil test = new SqlParserUtil();
		String result = test.getParsedSql(testSql);
		System.out.println(result);
		// List<SqlSegment> result=test.getParsedSqlList(testSql);//保存解析结果
	}
}

abstract class BaseSingleSqlParser {
	/** */
	/**
	 * * 原始Sql语句
	 */
	protected String originalSql;
	/** */
	/**
	 * * Sql语句片段
	 */
	protected List<SqlSegment> segments;

	/** */
	/**
	 * * 构造函数，传入原始Sql语句，进行劈分。 *
	 * 
	 * @param originalSql
	 */
	public BaseSingleSqlParser(String originalSql) {
		this.originalSql = originalSql;
		segments = new ArrayList<SqlSegment>();
		initializeSegments();
		splitSql2Segment();
	}
	/** */
	/**
	 * * 初始化segments，强制子类实现 *
	 */
	protected abstract void initializeSegments();
	/** */
	/**
	 * * 将originalSql劈分成一个个片段 *
	 */
	protected void splitSql2Segment() {
		for (SqlSegment sqlSegment : segments) {
			sqlSegment.parse(originalSql);
		}
	}
	/** */
	/**
	 * * 得到解析完毕的Sql语句 *
	 */
	public String getParsedSql() {
		// 测试输出各个片段的信息
		StringBuffer sb = new StringBuffer();
		for (SqlSegment sqlSegment : segments) {
			sb.append(sqlSegment.getParsedSqlSegment());
		}
		String retval = sb.toString().replaceAll("@+", "\n");
		return retval;
	}
	/** */
	/**
	 * 得到解析的Sql片段
	 * 
	 * @return
	 */
	public List<SqlSegment> RetrunSqlSegments() {
		int SegmentLength = this.segments.size();
		if (SegmentLength != 0) {
			List<SqlSegment> result = this.segments;
			return result;
		} else {
			// throw new Exception();
			return null;
		}
	}
}

class DeleteSqlParser extends BaseSingleSqlParser {
	public DeleteSqlParser(String originalSql) {
		super(originalSql);
	}
	@Override
	protected void initializeSegments() {
		segments.add(new SqlSegment("(delete from)(.+)( where | ENDOFSQL)", "[,]"));
		segments.add(new SqlSegment("(where)(.+)( ENDOFSQL)", "(and|or)"));
	}
}

class InsertSqlParser extends BaseSingleSqlParser {
	public InsertSqlParser(String originalSql) {
		super(originalSql);
	}
	@Override
	protected void initializeSegments() {
		segments.add(new SqlSegment("(insert into)(.+)([(])", "[,]"));
		segments.add(new SqlSegment("([(])(.+)( [)] values )", "[,]"));
		segments.add(new SqlSegment("([)] values [(])(.+)( [)])", "[,]"));
	}
	@Override
	public String getParsedSql() {
		String retval = super.getParsedSql();
		retval = retval + ")";
		return retval;
	}
}

/** */
/**
 * 单句查询插入语句解析器
 * 
 * @author 赵朝峰
 * @since 2013-6-10
 * @version 1.00
 */
class InsertSelectSqlParser extends BaseSingleSqlParser {
	public InsertSelectSqlParser(String originalSql) {
		super(originalSql);
	}
	@Override
	protected void initializeSegments() {
		segments.add(new SqlSegment("(insert into)(.+)( select )", "[,]"));
		segments.add(new SqlSegment("(select)(.+)(from)", "[,]"));
		segments.add(new SqlSegment("(from)(.+)( where | on | having | groups+by | orders+by | ENDOFSQL)", "(,|s+lefts+joins+|s+rights+joins+|s+inners+joins+)"));
		segments.add(new SqlSegment("(where|on|having)(.+)( groups+by | orders+by | ENDOFSQL)", "(and|or)"));
		segments.add(new SqlSegment("(groups+by)(.+)( orders+by| ENDOFSQL)", "[,]"));
		segments.add(new SqlSegment("(orders+by)(.+)( ENDOFSQL)", "[,]"));
	}
}

class NoSqlParserException extends Exception {
	private static final long serialVersionUID = 1L;

	NoSqlParserException() {
	}
	NoSqlParserException(String sql) {
		// 调用父类方法
		super(sql);
	}
}

class SelectSqlParser extends BaseSingleSqlParser {
	public SelectSqlParser(String originalSql) {
		super(originalSql);
	}
	@Override
	protected void initializeSegments() {
		segments.add(new SqlSegment("(select)(.+)(from)", "[,]"));
		segments.add(new SqlSegment("(from)(.+)( where | on | having | group by | order by | ENDOFSQL)", "(,| left join | right join | inner join )"));
		segments.add(new SqlSegment("(where|on|having)(.+)( group by | order by | ENDOFSQL)", "(and|or)"));
		segments.add(new SqlSegment("(group by)(.+)( order by| ENDOFSQL)", "[,]"));
		segments.add(new SqlSegment("(order by)(.+)( ENDOFSQL)", "[,]"));
	}
}

class SingleSqlParserFactory {
	public static BaseSingleSqlParser generateParser(String sql) {
		if (contains(sql, "(insert into)(.+)(select)(.+)(from)(.+)")) {
			return new InsertSelectSqlParser(sql);
		} else if (contains(sql, "(select)(.+)(from)(.+)")) {
			return new SelectSqlParser(sql);
		} else if (contains(sql, "(delete from)(.+)")) {
			return new DeleteSqlParser(sql);
		} else if (contains(sql, "(update)(.+)(set)(.+)")) {
			return new UpdateSqlParser(sql);
		} else if (contains(sql, "(insert into)(.+)(values)(.+)")) {
			return new InsertSqlParser(sql);
		}
		// sql=sql.replaceAll("ENDSQL", "");
		else
			return new InsertSqlParser(sql);
		// throw new NoSqlParserException(sql.replaceAll("ENDOFSQL",
		// ""));//对异常的抛出
	}
	/** */
	/**
	 * 看word是否在lineText中存在，支持正则表达式
	 * 
	 * @param sql:要解析的sql语句
	 * @param regExp:正则表达式
	 * @return
	 */
	private static boolean contains(String sql, String regExp) {
		Pattern pattern = Pattern.compile(regExp, Pattern.CASE_INSENSITIVE);
		Matcher matcher = pattern.matcher(sql);
		return matcher.find();
	}
}

class SqlSegment {
	private static final String Crlf = "@";
	private static final String FourSpace = "　　";
	/** */
	/**
	 * Sql语句片段开头部分
	 */
	private String start;
	/** */
	/**
	 * Sql语句片段中间部分
	 */
	private String body;
	/** */
	/**
	 * Sql语句片段结束部分
	 */
	private String end;
	/** */
	/**
	 * 用于分割中间部分的正则表达式
	 */
	private String bodySplitPattern;
	/** */
	/**
	 * 表示片段的正则表达式
	 */
	private String segmentRegExp;
	/** */
	/**
	 * 分割后的Body小片段
	 */
	private List<String> bodyPieces;

	/** */
	/**
	 * 构造函数
	 * 
	 * @param segmentRegExp
	 *            表示这个Sql片段的正则表达式
	 * @param bodySplitPattern
	 *            用于分割body的正则表达式
	 */
	public SqlSegment(String segmentRegExp, String bodySplitPattern) {
		start = "";
		body = "";
		end = "";
		this.segmentRegExp = segmentRegExp;
		this.bodySplitPattern = bodySplitPattern;
		this.bodyPieces = new ArrayList<String>();
	}
	/** */
	/**
	 * 从sql中查找符合segmentRegExp的部分，并赋值到start,body,end等三个属性中
	 * 
	 * @param sql
	 */
	public void parse(String sql) {
		Pattern pattern = Pattern.compile(segmentRegExp, Pattern.CASE_INSENSITIVE);
		for (int i = 0; i <= sql.length(); i++) {
			String shortSql = sql.substring(0, i);
			// 测试输出的子句是否正确
			Matcher matcher = pattern.matcher(shortSql);
			while (matcher.find()) {
				start = matcher.group(1);
				body = matcher.group(2);
				// 测试body部分
				// System.out.println(body);
				end = matcher.group(3);
				// 测试相应的end部分
				// System.out.println(end);
				parseBody();
				return;
			}
		}
	}
	/** */
	/**
	 * 解析body部分
	 */
	private void parseBody() {
		List<String> ls = new ArrayList<String>();
		Pattern p = Pattern.compile(bodySplitPattern, Pattern.CASE_INSENSITIVE);
		// 先清除掉前后空格
		body = body.trim();
		Matcher m = p.matcher(body);
		StringBuffer sb = new StringBuffer();
		boolean result = m.find();
		while (result) {
			m.appendReplacement(sb, m.group(0) + Crlf);
			result = m.find();
		}
		m.appendTail(sb);
		// 再按空格断行
		String[] arr = sb.toString().split(" ");
		int arrLength = arr.length;
		for (int i = 0; i < arrLength; i++) {
			String temp = FourSpace + arr[i];
			if (i != arrLength - 1) {
				// temp=temp+Crlf;
			}
			ls.add(temp);
		}
		bodyPieces = ls;
	}
	/** */
	/**
	 * 取得解析好的Sql片段
	 * 
	 * @return
	 */
	public String getParsedSqlSegment() {
		StringBuffer sb = new StringBuffer();
		sb.append(start + Crlf);
		for (String piece : bodyPieces) {
			sb.append(piece + Crlf);
		}
		return sb.toString();
	}
	public String getBody() {
		return body;
	}
	public void setBody(String body) {
		this.body = body;
	}
	public String getEnd() {
		return end;
	}
	public void setEnd(String end) {
		this.end = end;
	}
	public String getStart() {
		return start;
	}
	public void setStart(String start) {
		this.start = start;
	}
}

class UpdateSqlParser extends BaseSingleSqlParser {
	public UpdateSqlParser(String originalSql) {
		super(originalSql);
	}
	@Override
	protected void initializeSegments() {
		segments.add(new SqlSegment("(update)(.+)(set)", "[,]"));
		segments.add(new SqlSegment("(set)(.+)( where | ENDOFSQL)", "[,]"));
		segments.add(new SqlSegment("(where)(.+)( ENDOFSQL)", "(and|or)"));
	}
}
